package com.pi.shisan.service.config;

import com.pi.shisan.component.util.Lunar;
import com.pi.shisan.service.domain.LunarMapping;
import com.pi.shisan.service.domain.WeatherCity;
import com.pi.shisan.service.mapper.LunarMappingMapper;
import com.pi.shisan.service.mapper.WeatherCityMapper;
import com.pi.shisan.service.service.ClockInfoService;
import com.pi.shisan.util.FileUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.HashMap;
import java.util.Map;

/**
 * 初始化表结构
 */
@Component
public class TableConfig {

    @Autowired
    private DataSource dataSource;

    @Autowired
    private ClockInfoService clockInfoService;

    @Autowired
    private WeatherCityMapper weatherCityMapper;

    @Autowired
    private LunarMappingMapper lunarMappingMapper;

    public void init() throws Exception {
        Connection connection = dataSource.getConnection();

        //闹铃表
        String tableSql = "CREATE table IF NOT EXISTS " +
                "led_clock_info (\n" +
                "    id integer\n" +
                "        constraint led_clock_info\n" +
                "            primary key autoincrement,\n" +
                "    clk_name TEXT not null,\n" + //闹铃名称
                "    clk_year INTEGER,\n" +  //开始时间，年
                "    clk_month INTEGER,\n" +  //开始时间，月
                "    clk_week INTEGER,\n" +  //开始时间，周
                "    clk_day INTEGER,\n" +  //开始时间，天
                "    clk_hour INTEGER,\n" +  //开始时间，小时
                "    clk_minute INTEGER,\n" +  //开始时间，分钟
                "    clk_between INTEGER,"+ //如果clkType = 1，则这里存分钟数
                "    clk_type INTEGER not null,\n"+ //0 - 不重复 1 - 间隔分钟重复 2 - 每年 3 - 每月 4 - 每周 5 - 每日 6 - 每小时
                "    clk_switch INTEGER not null default 1, \n"+ //闹铃开关 1 - 打开
                "    clk_status INTEGER not null default 0,\n"+ //0 - 失效闹铃 1 - 有效闹铃
                "    clk_volume INTEGER\n"+ //闹铃音量 0或空表示系统默认的 等级为1 - 9
                ")";
        Statement statement = connection.createStatement();
        statement.executeUpdate(tableSql);
        statement.close();

        tableSql = "CREATE table IF NOT EXISTS " +
                "led_audio_info (\n" +
                "    id integer\n" +
                "        constraint led_audio_info\n" +
                "            primary key autoincrement,\n" +
                "    audio_name TEXT not null,\n" + //音频名称
                "    audio_path TEXT,\n" +  //文件路径
                "    repeat_times INTEGER not null,\n" +  //重复次数
                "    clock_id text not null\n" +    //闹铃ID
                ")";
        statement = connection.createStatement();
        statement.executeUpdate(tableSql);
        statement.close();

        tableSql = "CREATE table IF NOT EXISTS " +
                "led_clock_holiday (\n" +
                "    id integer\n" +
                "        constraint led_audio_info\n" +
                "            primary key autoincrement,\n" +
                "    holiday_name TEXT not null,\n" + //节日名称
                "    clk_year integer not null,\n" + //年份
                "    dayint TEXT,\n" +  //日期 如20220501 表示2022年5月1日
                "    day_type INTEGER not null\n" + //1 - 假日 2 - 调休日
                ")";
        statement = connection.createStatement();
        statement.executeUpdate(tableSql);
        statement.close();


        tableSql = "CREATE table IF NOT EXISTS " +
                "led_weather_city (\n" +
                "    id integer\n" +
                "        constraint led_audio_info\n" +
                "            primary key autoincrement,\n" +
                "    name1 TEXT not null,\n" + //省
                "    name2 TEXT ,\n" + //市
                "    name3 TEXT,\n" +  //区/县
                "    item_level INTEGER not null,\n" +  //1 - 省 2 - 市 3 区/县
                "    pid INTEGER not null default 0, \n" + //父ID
                "    city_id TEXT \n" + //父ID
                ")";
        statement = connection.createStatement();
        statement.executeUpdate(tableSql);
        statement.close();

        tableSql = "CREATE table IF NOT EXISTS " +
                "led_my_weather_city (\n" +
                "    id integer\n" +
                "        constraint led_audio_info\n" +
                "            primary key autoincrement,\n" +
                "    city_record_id INTEGER not null\n" + //节日名称
                ")";
        statement = connection.createStatement();
        statement.executeUpdate(tableSql);
        statement.close();


        //免打扰设置表
        tableSql = "CREATE table IF NOT EXISTS " +
                "led_dnd_mode_setting (\n" +
                "    id integer\n" +
                "        constraint led_audio_info\n" +
                "            primary key autoincrement,\n" +
                "    dnd_type INTEGER not null,\n" + //1 - LED简化模式 2 - 整点报时关闭模式
                "    dnd_start_time INTEGER not null,\n" + //免打扰开始时间
                "    dnd_end_time INTEGER not null\n" + //免打扰结束时间
                ")";
        statement = connection.createStatement();
        statement.executeUpdate(tableSql);
        statement.close();


        tableSql = "CREATE table IF NOT EXISTS " +
                "lunar_mapping (\n" +
                "    id integer\n" +
                "        constraint led_audio_info\n" +
                "            primary key autoincrement,\n" +
                "    dayint INTEGER not null, \n" + //阳历dayint
                "    cdayint INTEGER not null, \n"+ //农历dayint
                "    cday_name TEXT not null, \n" + //农历中文日名称
                "    cmonth_name TEXT not null, \n" + //农历中文月名称
                "    cyear_name TEXT not null, \n" + //农历中文年名称
                "    canimal_name TEXT not null \n" + //农历中文年（动物）名称
                ")";
        statement = connection.createStatement();
        statement.executeUpdate(tableSql);
        statement.close();

        tableSql = "CREATE INDEX idx_cdayint_dayint ON lunar_mapping (cdayint,dayint)";
        statement = connection.createStatement();
        try {
            statement.executeUpdate(tableSql);
        } catch (Exception e) {
        } finally {
            statement.close();
        }

        connection.close();

        if (weatherCityMapper.checkHasInitFinished() == null) {
            weatherCityMapper.clear();
            initWeatherCity();
        }
        if (lunarMappingMapper.checkHasInitFinished() == null) {
            lunarMappingMapper.clear();
            initChineseDayMapping();
        }

        clockInfoService.refreshChinaVacation();
        clockInfoService.initPlans();
    }


    private void initWeatherCity() {

        InputStream inputStream = getClass().getResourceAsStream("/weather.city.info");
        String text = FileUtils.readTextFromStream(inputStream, "UTF-8");
        //省份 -> ID映射
        Map<String, Long> level1Mapping = new HashMap<>();
        //省份+市区 -> ID映射
        Map<String, Long> level2Mapping = new HashMap<>();

        String[] lines = text.split("\n");
        for (String line : lines) {
            if (line.isEmpty()) {
                continue;
            }
            String[] infos = line.split(",");
            String cityId = infos[0];
            String p1 = infos[3];
            String p2 = infos[2];
            String p3 = infos[1];
            String l2Key = p1 +"/"+ p2;
            Long pid = level2Mapping.get(l2Key);
            if (pid == null) {
                Long topId = level1Mapping.get(p1);
                if (topId == null) {
                    WeatherCity top = new WeatherCity();
                    top.setName1(p1);
                    top.setItemLevel(1);
                    weatherCityMapper.insert(top);
                    topId = top.getId();
                    level1Mapping.put(p1, topId);
                }
                WeatherCity pCity = new WeatherCity();
                pCity.setPid(topId);
                pCity.setItemLevel(2);
                pCity.setName1(p1);
                pCity.setName2(p2);
                weatherCityMapper.insert(pCity);
                pid = pCity.getId();
                level2Mapping.put(l2Key, pid);
            }
            WeatherCity city = new WeatherCity();
            city.setPid(pid);
            city.setItemLevel(3);
            city.setName1(p1);
            city.setName2(p2);
            city.setName3(p3);
            city.setCityId(cityId);
            weatherCityMapper.insert(city);
        }
    }

    private void initChineseDayMapping() {
        LocalDate date = LocalDate.now();
        while (true) {
            Lunar lunar;
            try {
                lunar = new Lunar(date.getYear(), date.getMonthValue(), date.getDayOfMonth());
            } catch (Exception e) {
                break;
            }
            LunarMapping lunarMapping = new LunarMapping();
            lunarMapping.setDayint(date.getYear() * 10000 + date.getMonthValue() * 100 + date.getDayOfMonth());
            lunarMapping.setCdayint(lunar.getMonth() * 100 + lunar.getDay());
            lunarMapping.setCdayName(lunar.get_date());
            lunarMapping.setCmonthName(lunar.get_month());
            lunarMapping.setCyearName(lunar.cyclical());
            lunarMapping.setCanimalName(lunar.animalsYear());
            lunarMappingMapper.insert(lunarMapping);
            date = date.plusDays(1);
        }
    }
}
